<?php
defined('_JEXEC') or die();
jimport('joomla.application.component.model');  
class WrdModelProducts extends WrdModel 
{
    var $_data;
	var $_id;
	var $_total = null;
	var $_pagination = null;
	var $_limit = null;
	var $_limitstart = null;
	
	function __construct(){
		parent::__construct();
		$mainframe = &JFactory::getApplication();
		$context = JRequest::getCmd('option');
		$view = JRequest::getCmd('view');
		$this->_limit = $mainframe->getUserStateFromRequest( $context.$view.'limit', 'limit', $mainframe->getCfg('list_limit'), 0);
		$this->_limitstart = $mainframe->getUserStateFromRequest( $context.$view.'limitstart', 'limitstart', 0 );
		$this->setState('limit', $this->_limit);
		$this->setState('limitstart', $this->_limitstart);
	}
	function getData(){
		if (empty($this->_data)){
			$query = $this->_buildQuery();
			$this->_db->setQuery( $query, $this->_limitstart, $this->_limit );
			$this->_data = $this->_db->loadObjectList();
		}
		return $this->_data;
	}
	function getTotal(){
		if (empty($this->_total)){
			$query = $this->_buildQuery();
			$this->_total = $this->_getListCount($query);
		}
		return $this->_total;
	}
	function getPagination(){
		if (empty($this->_pagination))
		{
			jimport('joomla.html.pagination');
			$this->_pagination = new JPagination( $this->getTotal(), $this->_limitstart, $this->_limit );
		}
		return $this->_pagination;
	}
	function _buildQuery(){
		$mainframe = &JFactory::getApplication();
		$context = JRequest::getCmd('option');
		$view = JRequest::getCmd('view');
		$orderby 			= $this->_buildContentOrderBy();
		$search 			= $mainframe->getUserStateFromRequest( $context.$view.'search','search','','string');
		$filter_state 		= $mainframe->getUserStateFromRequest( $context.$view.'published','published',-1,'int');
		$filter_user 		= $mainframe->getUserStateFromRequest( $context.$view.'created_id','created_id',-1,'int');
		
		$search 			= JString::strtolower( $search );
		
		$where = array();
		if ( $search ){
			$where[] = 'c.name LIKE '.$this->_db->Quote( '%'.$this->_db->getEscaped( $search, true ).'%', false );
		}
		if ( $filter_state != -1 ){
			$where[] = 'c.published = '.$filter_state;
		}
		if ( $filter_user  != -1){
			$where[] = 'c.created_id = '.$filter_user;
		}
		
		$where 		= ( count( $where ) ? ' WHERE ' . implode( ' AND ', $where ) : '' );
		$query = ' SELECT 
					u.name as user_name, 
                    c.id, 
                    c.name as products_name,
                    cat.title as cat_name,
                    c.point,
                    c.published_up,
                    c.published_down,
                    c.quantity,
                    c.published,
                    c.created_date,
                    c.pointx ,
					c.cash,
					c.p_code
                	FROM #__wrd_products as c 
                	LEFT JOIN #__users as u ON c.created_id = u.id
                	LEFT JOIN #__wrd_pcategory as cat ON cat.id = c.cat_id'
		. $where
		. $orderby;
		return $query;
	}
	function _buildContentOrderBy(){
		$mainframe = &JFactory::getApplication();
		$context = JRequest::getCmd('option');
		$view = JRequest::getCmd('view');
		$filter_order     = $mainframe->getUserStateFromRequest( $context.$view.'filter_order_author','filter_order','id' );
		$filter_order_Dir = $mainframe->getUserStateFromRequest( $context.$view.'filter_order_Dir',  'filter_order_Dir', '' );
		$orderby 	= ' ORDER BY '.$filter_order.' '.$filter_order_Dir;
		return $orderby;
	}
    
	function delete($cid = array()){
		$result = false;
		if (count( $cid )){
			JArrayHelper::toInteger($cid);
			$cids = implode( ',', $cid );
			$query = 'DELETE FROM #__wrd_products'
				. ' WHERE id IN ( '.$cids.' )';
			$this->_db->setQuery( $query );
			if(!$this->_db->query()) {
				$this->setError($this->_db->getErrorMsg());
				return false;
			}
		}
		return true;
	}
 
	function publish($cid = array(), $publish = 1){
		$user 	=& JFactory::getUser();
		if (count( $cid ))
		{
			JArrayHelper::toInteger($cid);
			$cids = implode( ',', $cid );
			$query = 'UPDATE #__wrd_products'
				. ' SET published = '.(int) $publish
				. ' WHERE id IN ( '.$cids.' )'
			;
			$this->_db->setQuery( $query );
			if (!$this->_db->query()) {
				$this->setError($this->_db->getErrorMsg());
				return false;
			}
		}
		return true;
	}
    
     //save to table $nametable
    function store($post,$tablename){
        $profile = JTable::getInstance($tablename, 'TableWrd');
        $profile->load();
        $profile->bind($post);
		if ($profile->store()) {
			//$profile->reorder();
			return $profile->id;
		} else {
			return 0;
		}
  	}
  	
    /*
     * Select products are published, and between publish up and publish down
     */
    function publishProducts(){
        $sql = "
            SELECT 
                `t`.id   AS `value`,
                `t`.NAME AS `text`,
                `t`.cat_id,
                `t`.POINT AS `point`,
                `t`.pointx,
                `t`.cash
            FROM #__wrd_products `t`
            WHERE `t`.published = 1
                  AND ((YEAR(`t`.published_up) = 0
                        AND YEAR(`t`.published_down) = 0)
                        OR (`t`.published_up <= '".date('Y-m-d')."'
                            AND `t`.published_down >= '".date('Y-m-d')."'))
        ";
        $this->_db->setQuery( $sql );
        return $this->_db->loadObjectList();
      }
      
  	function getList(){
  		$query = ' SELECT id AS `value`, name AS `text` '
		. ' FROM #__wrd_products WHERE published=1 ORDER BY `text` ASC ';
		$this->_db->setQuery( $query );
		return $this->_db->loadObjectList();
  	}
	function getUserList(){
		$query = 'SELECT a.created_id as value , b.username as text FROM #__wrd_products a LEFT JOIN #__users b ON a.created_id = b.id 
			GROUP BY a.created_id';
		
		$this->_db->setQuery( $query );
		return $this->_db->loadObjectList();
	}
  	
  	function getItem($id){
	
  		$query = 'SELECT * FROM #__wrd_products WHERE id = '.$id;
		$this->_db->setQuery($query);
		return $this->_db->loadObject();
  	
	}
	function getListProductsByCatId($cat_id = 0,$limit_start = 0, $limit = 9,$flag = 0, $ext_pid = 0){
  		if(!empty($cat_id) && ($cat_id != 0)){
  			if($ext_pid != 0){
  				if($flag==0){
					$query = 'SELECT 
								p.id, 
			                    p.name as products_name,
			                    p.cat_id,
			                    c.title as cat_name,
			                    p.point,
			                    p.published_up,
			                    p.published_down,
			                    p.quantity,
			                    p.des,
			                    p.pointx,
			                    p.cash
			                    FROM #__wrd_products as p 
			                	LEFT JOIN #__wrd_pcategory as c ON c.id = p.cat_id'
							.' where ( p.published = 1 and c.published = 1 and p.cat_id = '.$cat_id.' and p.id != '.$ext_pid.')'; 
					$this->_db->setQuery($query,$limit_start,$limit);
					return $this->_db->loadObjectList();
	  			}else{
	  				$query = 'SELECT count(p.id) as total
	  							FROM #__wrd_products as p 
			                	LEFT JOIN #__wrd_pcategory as c ON c.id = p.cat_id'
							.' where ( p.published = 1 and c.published = 1 and p.cat_id = '.$cat_id.' and p.id != '.$ext_pid.')';
					$this->_db->setQuery($query);
					$tt = $this->_db->loadObject();
					return $tt->total;
	  			}
  			}else{
	  			if($flag==0){
					$query = 'SELECT 
								p.id, 
			                    p.name as products_name,
			                    p.cat_id,
			                    c.title as cat_name,
			                    p.point,
			                    p.published_up,
			                    p.published_down,
			                    p.quantity,
			                    p.des,
			                    p.pointx,
			                    p.cash
			                    FROM #__wrd_products as p 
			                	LEFT JOIN #__wrd_pcategory as c ON c.id = p.cat_id'
							.' where ( p.published = 1 and c.published = 1 and p.cat_id = '.$cat_id.')'; 
					$this->_db->setQuery($query,$limit_start,$limit);
					return $this->_db->loadObjectList();
	  			}else{
	  				$query = 'SELECT count(p.id) as total
	  							FROM #__wrd_products as p 
			                	LEFT JOIN #__wrd_pcategory as c ON c.id = p.cat_id'
							.' where ( p.published = 1 and c.published = 1 and p.cat_id = '.$cat_id.')';
					$this->_db->setQuery($query);
					$tt = $this->_db->loadObject();
					return $tt->total;
	  			}
  			}
  		}else{
  			return NULL;
  		}
  	}
	function getNameCat($cat_id = 0){
  		if(!empty($cat_id) && ($cat_id != 0)){
			$query = 'SELECT 
						c.title as cat_name
						FROM #__wrd_pcategory as c '
					.' where ( c.published = 1 and c.id = '.$cat_id.')'
			; 
			$this->_db->setQuery($query);
			return $this->_db->loadObject();
  		}else{
  			return NULL;
  		}
  	}
	function getProductsDetails($cat_id = 0,$p_id = 0){
  		if(!empty($cat_id) && ($cat_id != 0) && !empty($p_id) && $p_id != 0){
  			$query = 'SELECT 
							p.id, 
		                    p.name as products_name,
		                    p.cat_id,
		                    c.title as cat_name,
		                    p.point,
		                    p.published_up,
		                    p.published_down,
		                    p.quantity,
		                    p.des,
		                    p.pointx,
		                    p.cash
		                    FROM #__wrd_products as p 
		                	LEFT JOIN #__wrd_pcategory as c ON c.id = p.cat_id'
						.' where ( p.published = 1 and c.published = 1 and p.cat_id = '.$cat_id.' and p.id = '.$p_id.' )'; 
				$this->_db->setQuery($query);
				return $this->_db->loadObject();
  		}else{
  			echo "here" ;
  			return NULL;
  		}
  	}
	function getProductsConfirm($p_id = 0){
		if(!empty($p_id) && $p_id != 0){
  			$query = '
  					SELECT 
					p.id, 
                    p.name as products_name,
                    p.cat_id,
                    p.point,
                    p.published_up,
                    p.published_down,
                    p.quantity,
                    p.des,
                    p.pointx,
                    p.cash
                    FROM #__wrd_products as p '
				.' where ( p.published = 1 and p.id = '.$p_id.' )'; 
				$this->_db->setQuery($query);
				return $this->_db->loadObject();
  		}else{
  			echo "here" ;
  			return NULL;
  		}
  	}
	function getListImages($p_id = 0, $num_row){
  		if(!empty($p_id) && ($p_id != 0)){
  			if($num_row==1){
	  			$query = 'SELECT 
							f.name as file_name
							FROM #__wrd_file as f 
		                	LEFT JOIN #__wrd_products as p ON p.id = f.product_id'
						.' where (f.type = "image" and f.product_id = '.$p_id.')'
						.' limit 0,1';
				$this->_db->setQuery($query);
				return $this->_db->loadObject();
  			}else{
  				$query = 'SELECT 
							f.name as file_name
							FROM #__wrd_file as f 
		                	LEFT JOIN #__wrd_products as p ON p.id = f.product_id'
						.' where (f.type = "image" and f.product_id = '.$p_id.')'
						.' limit 0,3'; 
				$this->_db->setQuery($query);
				return $this->_db->loadObjectList();
  			}
  		}else{
  			return NULL;
  		}
  	}
  	function getPaging( $total, $limitstart, $limit )
   	{
      	// Lets load the content if it doesn't already exist
      	jimport('joomla.html.pagination');
        $this->_pagination = new JPagination( $total, $limitstart, $limit );
         
      	return $this->_pagination;
    }
    
    function saveAddRedemption($post){
		$app = JFactory::getApplication() ;
        $result['code'] = '';
		$db = JFactory::getDbo();
		/************** Product List **************/
        if(!empty($post['product_name_list']))
        {
			$arr = array();
			$productList = array();
			foreach($post['product_name_list'] as $key => $item) {
				if(($post[$key] > 0) && ($post[$key.'_hidden'] > 0)) {
					$productId = $post[$key.'_hidden'];
					if(!isset($arr[$productId][$post[$key.'_type']]['quantity'])){
						
						if(isset($post[$key.'_voucher'])){
							$arr[$productId][$key]['voucher'] = $post[$key.'_voucher'];
							$arr[$productId][$key]['date'] = $post[$key.'_date'];
						}
						else {
							$arr[$productId][$post[$key.'_type']]['date'] = $post[$key.'_date'];
							$arr[$productId][$post[$key.'_type']]['quantity'] = $post[$key];
							$arr[$productId][$post[$key.'_type']]['type'] = $post[$key.'_type'];
						}
					}else{
						$arr[$productId][$post[$key.'_type']]['date'] = $post[$key.'_date'];
						$arr[$productId][$post[$key.'_type']]['quantity']  += $post[$key];
						$arr[$productId][$post[$key.'_type']]['type'] = $post[$key.'_type'];
					}
				}                   
            }
		}
		$total = 0;
		foreach($post['point_values'] as $key => $value){
			$total+=$value;
		}
		/***********@hung.phan***************/
		$tmp_profile_user = WrdHelper::getUserProfile($post['mid'],'member_id');
		if($tmp_profile_user->p_o_u < $total)
		{
			jexit('Cant redemption :O');
		}
		
		if(!empty($arr)){
            $vercode = WrdControllerProfile::random_letters() ;
			foreach($arr as $k => $v){
				$product = self::getProductById($k);
				
				foreach($v as $k1 => $v1){
					if(!is_int($k1)) {
						$obje = new stdClass();
						$obje->member_id = $post['mid'];
						$obje->product_id = $k;
						$obje->product_cost = $product->price;
						$obje->cost_pricing = $product->cost_pricing;
						$obje->code_redemption = $vercode;
						$obje->code_voucher = $v1['voucher'];
						$obje->expired = empty($v1['date'])?'':date("Y-m-d",strtotime($v1['date']));
						$obje->status = 0;
						$this->_db->insertObject('#__wrd_redem_report_voucher', $obje);
						$rrvid = $this->_db->insertid();
						foreach($post['point_values'] as $k2 => $v2){
							if($v2 != ''){
								$sql = "SELECT o.name as outletName,t.outlet 
									FROM #__wrd_outlets as o 
									INNER JOIN #__wrd_transaction as t ON o.code = t.outlet
									WHERE t.transaction_id = '".$post[$k2]."'
								";
								$db->setQuery($sql);
								$row = $db->loadObject();
								$outlet = (empty($row->outlet)) ? 000: $row->outlet;
								$outletName = (empty($row->outletName)) ? "WMC GROUP": $row->outletName;
								$percent = number_format($v2/$total*100,2);
								$obj = new stdClass();
								$obj->rrvid = $rrvid;
								$obj->outlet = $outlet;
								$obj->outletName = $outletName;
								$obj->percent = $percent;
								$obj->charge_cost = $product->cost_pricing*$percent/100;
								$this->_db->insertObject('#__wrd_rrv_outlet', $obj);
							}
							
							}
						
						$item = array('quantity' => 1,
										'type' => $post[$k1."_type"],
										'voucher' => $v1['voucher'],
										'date'=>empty($v1['date'])?'':date("Y-m-d",strtotime($v1['date'])),
										'idrv'=>$rrvid);
						
						if($product->id > 0) {
							self::createRemption($product, $item,$post, $vercode);
						}
						
					}
					else {
					  if($product->id > 0) {
						$item = array('quantity' => $v1['quantity'],
										'date'=>empty($v1['date'])?'':date("Y-m-d",strtotime($v1['date'])),
										'type' => $v1['type']);
						self::createRemption($product, $item,$post, $vercode);
					  }	
					}	
			}
			
		}
		//echo '<pre>';print_r($arr);die;
			foreach($post['point_values'] as $k => $v){
				if($v != ''){
					/****** Create New Point ******/
					{	
						$obj = new stdClass();
						$obj->id = null;
						$obj->user_id = $post['uid'] ;
						$obj->redemption_id = $vercode;
						$obj->transaction = $post[$k];
						$obj->outlet = $post[$k.'_outlet'];;
						$obj->type = 9;
						$obj->point = -$v;
						$obj->expire = '0000-00-00 00:00:00' ;
						$obj->point = -$v;
						$obj->des = 'Redemption - '.$vercode;						
						$obj->created_id = $c_user->id ;
						date_default_timezone_set('Asia/Ho_Chi_Minh'); 
						//$obj->created_date = date('Y-m-d H:m:s') ;
						$obj->created_date = $post['created'];
						$obj->modify_id = '';
						$obj->modify_date = '';
						$this->_db->insertObject('#__wrd_point', $obj);
						
						// Update lai point summary cho user
						WrdHelper::updateSummaryPoint($obj->user_id,date('Y'),date('m'));
						$year = date("Y",strtotime($post['created']));
						$month = date("m",strtotime($post['created']));
						if($year < date('Y')||(($year == date('Y'))&&($month <= date('m')))){
						
							while(($month != date('m'))||($year != date('Y'))){
							
								WrdHelper::updateSummaryPoint($obj->user_id,$year,$month);
								if($month+1 <=12) ++$month;
								else {$month = 1;++$year;}
							}
						}
						
						WrdHelper::updateSummaryPoint($obj->user_id,date('Y'),date('m'));
						$db = JFactory::getDbo();
						$p_o_u = WrdHelper::getPoints($obj->user_id);
						$sql = 'UPDATE #__wrd_profile SET p_o_u = '.$p_o_u->point .' ,point_life_time = '.$p_o_u->point_life_time.' WHERE user_id = '.$obj->user_id;
						$db->setQuery($sql);
						$db->query();
						
						$profile =  WrdHelper::getUserProfile($obj->user_id,'userid');
						WrdHelper::updatePointOfUser($obj->user_id,$profile->member_id,$profile->dob,$profile->f_name,$profile->l_name);
						
						
						unset($obj);
						$pointId = $this->_db->insertid();
						
					}
				}
			}
			
            $result['code'] = $vercode;
        }
		
		$profile =  WrdHelper::getUserProfile($post['uid'],'userid');
		WrdHelper::updatePointOfUser($profile->user_id,$profile->member_id,$profile->dob,$profile->f_name,$profile->l_name);
        return $result;
    }
    
    /*
     * @param pointModel: current point
     * @param point: input value of current point
     * @param subtractPoint: point that is subtract in every point object
     */
    function saveLogRedemption($pointModel, $redemptionId, $subtractPoint)
    {
        /****** Create Log Point Redemption ******/
        {
            $c_user = JFactory::getUser();
            
            $obj = new stdClass();
            $obj->id = null;
            $obj->pid = $pointModel->id;
            $obj->rid = $redemptionId;
            $obj->point = $subtractPoint;
            $obj->created_id = $c_user->id;
            date_default_timezone_set('Asia/Ho_Chi_Minh'); 
            $obj->created_date = gmdate('d-m-Y H:i:s', time()+ 7*3600);
            $this->_db->insertObject('#__wrd_lpointredemtion', $obj);
            unset($obj);    
        }
        
        
    }
    /*
     * @param product: current product
     * @param item: input value of current product
     * @param point: input value of current point
     */
    function createRemption($product, $item,$post, $vercode)
    {
		/**** Init Data ****/    
        $c_user = JFactory::getUser();   
        /****** Check Point ******/ 
        if($item['type'] == 1) {
            $t_point = ($product->point * $item['quantity'] ) ;
            $t_cash = 0;
            $point_redeem = ( $product->point * $item['quantity'] ) ;
        }
        else {
            $t_point = ($product->pointx * $item['quantity'] ) ;
            $t_cash = ($product->cash * $item['quantity'] ) ;
            $point_redeem = ( $product->pointx * $item['quantity'] ) ;
        }
		
        /****** Create Redemption ******/
        
        $obj = new stdClass();
        $obj->id = null;
        $obj->user_id = $post['uid'] ;
        $obj->created_id = $c_user->id ;
        $obj->product_id = $product->id ;
		date_default_timezone_set('Asia/Ho_Chi_Minh'); 
		
		$obj->exchange_date = $post['created'];
        $obj->approved_date = $post['created']; 
		
        $obj->approved = 1;
        $obj->code = $vercode;
        if(isset($item['voucher'])) {$obj->code_voucher = $item['voucher'];}
		if(isset($item['date'])) {$obj->expired = $item['date'];}
		if(isset($item['idrv'])) {$obj->idrv = $item['idrv'];}
		$obj->point = $t_point;
        $obj->cash = $t_cash;
        $obj->point_id = $pointId;
        $obj->status_of_redemption = ($post['deliver_date'] != '' ? 1 : 0);
        $obj->status_of_redemption_date = ($post['deliver_date'] != '' ? $post['deliver_date'] : '0000-00-00');
        $obj->pick_up_date = ($post['pick_up_date'] != '' ? $post['pick_up_date'] : '0000-00-00');
        $obj->pick_up_at = $post['pick_up_at'];
		$obj->requested_outlet = $post['requested_outlet'];
        $obj->phone_num = '' ;
        $obj->quantity = $item['quantity'];
        $this->_db->insertObject('#__wrd_redemption', $obj) ;
        
        $redemptionId = $this->_db->insertid();
		
		//update quality
		$this->updateCurrentProduct($product,$item['quantity']);
		unset($obj);
        return array('redemptionId' => $redemptionId);
    }
    
    function updateCurrentProduct($product, $updatedValues)
    {
    	$query = 'SELECT quantity FROM #__wrd_products WHERE id = '.$product->id;
		$mquality = $this->_db->setQuery($query)->loadObject()->quantity;
		
		       
        $query = 'UPDATE #__wrd_products SET quantity = '.(intval($mquality) - intval($updatedValues)).' WHERE id = '.$product->id;
		file_put_contents('logfile.log', print_r($query,1), FILE_APPEND);
        $this->_db->setQuery($query);
        $this->_db->query();
        
    }
    
    function updateCurrentPoint($pointObject, $updatedValues)
    {
        $query = 'UPDATE #__wrd_point SET point = '.(intval($updatedValues['point'])).' WHERE id = '.$pointObject->id;
        $this->_db->setQuery($query);
        $this->_db->query();  
    }
    
    function updateRedemption($updatedValues){
		$query = "
            UPDATE #__wrd_redemption `t`
            SET status_of_redemption = ".$updatedValues['status'].",
                pick_up_date = '".$updatedValues['pick_up_date']."',
                status_of_redemption_date = '".$updatedValues['status_of_redemption_date']."',
                pick_up_at = ".$updatedValues['outlet'].",
				updated_by = ".$updatedValues['updated_by'].",
				updated_date = '".$updatedValues['updated_date']."',
				code_voucher = '".$updatedValues['code_voucher']."',
				requested_outlet = '".$updatedValues['requested_outlet']."',
				expired = '".date("Y-m-d",strtotime($updatedValues['expired']))."'
                
            WHERE `t`.id = '".$updatedValues['id']."'
        ";
       //echo $query;exit;
        $this->_db->setQuery($query);
        $this->_db->query();
		if(isset($updatedValues['idrv'])){
			$q = "
				UPDATE #__wrd_redem_report_voucher `t`
				SET 
					code_voucher = '".$updatedValues['code_voucher']."',
					expired = '".date("Y-m-d",strtotime($updatedValues['expired']))."'
				WHERE id='".$updatedValues['idrv']."'
			";
			$this->_db->setQuery($q);
			$this->_db->query();	
		}
    }
    
    function getProductById($id){
        $sql = "
            SELECT *
            FROM #__wrd_products `t`
            WHERE `t`.id = ".intval($id)."
        ";
        $this->_db->setQuery($sql);
        $model = $this->_db->loadObject();        
        return $model;
    }
    
    function getPointById($id){
        $sql = "
            SELECT *
            FROM #__wrd_point `t`
            WHERE `t`.id = ".intval($id)."
        ";
        $this->_db->setQuery($sql);
        $model = $this->_db->loadObject();        
        return $model;
    }
}